package com.dameng;

import com.dameng.domain.*;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.sql.Connection;
import java.util.*;
import java.util.stream.Collectors;

import static java.sql.DriverManager.getConnection;

/**
 * Hello world!
 *
 */
public class App 
{
    public static void main( String[] args ) throws Exception {
        //System.out.println( "Hello World!" );


        List<File> fileList = FileUtil.scanHtmlFilesRecursive(new File("C:\\Users\\Windows11\\Desktop\\数据库(1)\\数据库"));


        for (File fileName : fileList) {
            String fileNameStr = fileName.getName();
            String[] parts = fileNameStr.split("_");
            String fileTime = parts[parts.length - 1].replace(".html", "");



            Document doc = Jsoup.parse(new FileInputStream(fileName), "UTF-8", "");

            Map<String, List<Map<String, String>>> parsedData = parseAwrReport(doc);
            Map<String, Map<String, String>> baseInfo = parseBaseInfo(doc);

            //封装实例信息
            InstanceInfo instanceInfo = getInstanceInfo(baseInfo);
            instanceInfo.setFileTime(fileTime);
            InstanceEfficiency instanceEfficiency = parseEfficiencyTableBySummary(doc, "This table displays instance efficiency percentages",instanceInfo);
            List<LoadProfileEntry> loadProfileEntryList = parseLoadProfileTableBySummary(doc, "This table displays load profile",instanceInfo);



            //获取SQL列表  PART
            List<Map<String, String>> completeListOfSqlText = parsedData.get("Complete List of SQL Text");
            HashMap<String, String> completeSqlMap = new HashMap<>();
            if (completeListOfSqlText != null) {
                for (Map<String, String> sql : completeListOfSqlText) {
/*                    CompleteSql completeSql = new CompleteSql();
                    completeSql.setSqlId(sql.get("SQL Id"));
                    completeSql.setSqlText(sql.get("SQL Text"));*/
                    completeSqlMap.put(sql.get("SQL Id"), sql.get("SQL Text"));
                }
            }
            List<Map<String, String>> sqlOrderedByElapsedTime = parsedData.get("SQL ordered by Elapsed Time");
            //top sql封装的数据
            List<TopSqlByElapsedTime> topSqlByElapsedTimeList = new ArrayList<>();
            if (sqlOrderedByElapsedTime != null) {
                for (Map<String, String> sql : sqlOrderedByElapsedTime) {

                    TopSqlByElapsedTime topSqlByElapsedTime = new TopSqlByElapsedTime();
                    topSqlByElapsedTime.setDbId(instanceInfo.getDbId());
                    topSqlByElapsedTime.setFileTime(instanceInfo.getFileTime());
                    topSqlByElapsedTime.setInstance(instanceInfo.getInstance());
                    topSqlByElapsedTime.setSqlId(sql.get("SQL Id"));
                    if (completeSqlMap.containsKey(sql.get("SQL Id"))) {
                        String sql_Text = completeSqlMap.get(sql.get("SQL Id"));
                        topSqlByElapsedTime.setSqlText(sql_Text);
                    }

                    topSqlByElapsedTime.setElapsedTimePerExec(toDecimal(sql.get("Elapsed Time per Exec (s)")));
                    topSqlByElapsedTime.setElapsedTimeSeconds(toDecimal(sql.get("Elapsed Time (s)").replace(",", "")));
                    topSqlByElapsedTime.setExecutions(parseIntSafe(sql.get("Executions")));
                    topSqlByElapsedTime.setSqlModule(sql.get("SQL Module"));
                    topSqlByElapsedTime.setTotalPercentage(toDecimal(sql.get("%Total")));
                    topSqlByElapsedTime.setCpuPercentage(toDecimal(sql.get("%CPU")));
                    topSqlByElapsedTime.setIoPercentage((toDecimal(sql.get("%IO"))));
                    topSqlByElapsedTimeList.add(topSqlByElapsedTime);

                }
            }

            Connection conn = DatabaseUtil.getConnection();
            //数据入库,先将实例ID的信息入库
            DatabaseUtil.insertInstanceInfo(conn,instanceInfo);
            //数据入库,实例的效率百分比
            DatabaseUtil.insertInstanceEfficiency(conn, instanceEfficiency);
            //数据入库，负载曲线
            DatabaseUtil.insertLoadProfileList(conn, loadProfileEntryList);
            //数据入库，慢SQL
            DatabaseUtil.insertTopSqlList(conn, topSqlByElapsedTimeList);




        }


        System.out.println(1);

//        File awrFile = new File("E:\\all_code\\java_code\\parse_oracle_awr\\parse_oracle_awr\\html\\shzl2_53694_53695_202504061700_202504061800.html");
/*        Document doc = Jsoup.parse(new FileInputStream(awrFile), "UTF-8", "");

        Map<String, List<Map<String, String>>> parsedData = parseAwrReport(doc);
        Map<String, Map<String, String>> baseInfo = parseBaseInfo(doc);

        //封装实例信息
        InstanceInfo instanceInfo = getInstanceInfo(baseInfo);
        InstanceEfficiency instanceEfficiency = parseEfficiencyTableBySummary(doc, "This table displays instance efficiency percentages",instanceInfo);
        List<LoadProfileEntry> loadProfileEntryList = parseLoadProfileTableBySummary(doc, "This table displays load profile");
        System.out.println(instanceInfo);*/





    }


    public static InstanceEfficiency parseEfficiencyTableBySummary(Document doc, String summaryKeyword,InstanceInfo instanceInfo) {
        Element table = doc.selectFirst("table[summary*=" + summaryKeyword + "]");
        if (table == null) return null;

        InstanceEfficiency efficiency = new InstanceEfficiency();
        efficiency.setDbId(instanceInfo.getDbId());
        efficiency.setInstance(instanceInfo.getInstance());
        efficiency.setFileTime(instanceInfo.getFileTime());
        Elements rows = table.select("tr");

        for (Element row : rows) {
            Elements tds = row.select("td");
            for (int i = 0; i + 1 < tds.size(); i += 2) {
                String key = tds.get(i).text().replace(":", "").trim();
                String value = tds.get(i + 1).text().trim();

                switch (key) {
                    case "Buffer Nowait %":
                        efficiency.setBufferNowait(toDecimal(value));
                        break;
                    case "Redo NoWait %":
                        efficiency.setRedoNowait(toDecimal(value));
                        break;
                    case "Buffer Hit %":
                        efficiency.setBufferHit(toDecimal(value));
                        break;
                    case "In-memory Sort %":
                        efficiency.setInMemorySort(toDecimal(value));
                        break;
                    case "Library Hit %":
                        efficiency.setLibraryHit(toDecimal(value));
                        break;
                    case "Soft Parse %":
                        efficiency.setSoftParse(toDecimal(value));
                        break;
                    case "Execute to Parse %":
                        efficiency.setExecuteToParse(toDecimal(value));
                        break;
                    case "Latch Hit %":
                        efficiency.setLatchHit(toDecimal(value));
                        break;
                    case "Parse CPU to Parse Elapsd %":
                        efficiency.setParseCpuToElapsed(toDecimal(value));
                        break;
                    case "% Non-Parse CPU":
                        efficiency.setNonParseCpu(toDecimal(value));
                        break;
                    default:
                        System.out.println("未知字段: " + key);
                }
            }
        }

        return efficiency;
    }



    public static List<LoadProfileEntry> parseLoadProfileTableBySummary(Document doc, String summaryKeyword,InstanceInfo instanceInfo) {
        Element table = doc.selectFirst("table[summary*=" + summaryKeyword + "]");
        if (table == null) return Collections.emptyList();

        List<LoadProfileEntry> result = new ArrayList<>();
        Elements rows = table.select("tr");

        for (int i = 1; i < rows.size(); i++) {  // 跳过表头
            Element row = rows.get(i);
            Elements tds = row.select("td");
            if (tds.size() < 5) continue;

            LoadProfileEntry entry = new LoadProfileEntry();
            entry.setFileTime(instanceInfo.getFileTime());
            entry.setDbId(instanceInfo.getDbId());
            entry.setInstance(instanceInfo.getInstance());
            entry.setMetricName(tds.get(0).text().replace("：", "").trim());  // 中文冒号处理
            entry.setPerSecond(toDecimal(tds.get(1).text().trim()));
            entry.setPerTransaction( toDecimal(tds.get(2).text().trim()));
            entry.setPerExec( toDecimal(tds.get(3).text().trim()));
            entry.setPerCall(toDecimal(tds.get(4).text().trim()));

            result.add(entry);
        }

        return result;
    }

    public static Integer parseIntSafe(String numberStr) {
        if (numberStr == null || numberStr.isEmpty()) return null;
        return Integer.parseInt(numberStr.replace(",", "").trim());
    }

    private static BigDecimal toDecimal(String text) {
        try {
            if (text == null || text.trim().isEmpty()) return null;
            // 去掉逗号（如 "1,550.0" → "1550.0"）
            return new BigDecimal(text.replace(",", ""));
        } catch (Exception e) {
            return null;
        }
    }


    private static InstanceInfo getInstanceInfo(Map<String, Map<String, String>> baseInfo) {
        Map<String, String> dbInfo = baseInfo.get("awr_db_info");
        Map<String, String> hostInfo = baseInfo.get("awr_host_info");
        InstanceInfo instanceInfo = new InstanceInfo();
        if (dbInfo != null && hostInfo != null) {
            instanceInfo.setDbName(dbInfo.getOrDefault("DB Name", ""));
            instanceInfo.setDbId(dbInfo.getOrDefault("DB Id", ""));
            instanceInfo.setInstance(dbInfo.getOrDefault("Instance", ""));
            instanceInfo.setInstNum (dbInfo.getOrDefault("Inst num", ""));
            instanceInfo.setStartupTime (dbInfo.getOrDefault("Startup Time", ""));
            instanceInfo.setRelease (dbInfo.getOrDefault("Release", ""));
            instanceInfo.setRac (dbInfo.getOrDefault("RAC", ""));
            instanceInfo.setHostName (hostInfo.getOrDefault("Host Name", ""));
            instanceInfo.setPlatform(hostInfo.getOrDefault("Platform", ""));
            instanceInfo.setCpus(hostInfo.getOrDefault("CPUs", ""));
            instanceInfo.setCores (hostInfo.getOrDefault("Cores", ""));
            instanceInfo.setSockets( hostInfo.getOrDefault("Sockets", ""));
            instanceInfo.setMemoryGb(hostInfo.getOrDefault("Memory (GB)", ""));
        }
        return instanceInfo;
    }

    public static Map<String, List<Map<String, String>>> parseAwrReport(Document doc) {
        Map<String, List<Map<String, String>>> result = new LinkedHashMap<>();
        //            Document doc =Jsoup.parse(new FileInputStream(file), "UTF-8", "");
        Elements headers = doc.select("h2, h3.awr");

        for (Element header : headers) {
            String title = header.text().trim();
            Element next = header.nextElementSibling();

            while (next != null && !next.tagName().equals("table")) {
                next = next.nextElementSibling();
            }

            if (next != null) {
                List<Map<String, String>> rows = parseTable(next);
                result.put(title, rows);
            }
        }

        return result;
    }

    public static List<Map<String, String>> parseTable(Element table) {
        List<Map<String, String>> rows = new ArrayList<>();

        Elements allRows = table.select("tr");
        if (allRows.isEmpty()) return rows;

        Elements headers = allRows.get(0).select("th,td");
        List<String> columnNames = headers.stream().map(Element::text).collect(Collectors.toList());

        for (int i = 1; i < allRows.size(); i++) {
            Elements cols = allRows.get(i).select("td");
            if (cols.size() != columnNames.size()) continue;

            Map<String, String> rowData = new LinkedHashMap<>();
            for (int j = 0; j < cols.size(); j++) {
                rowData.put(columnNames.get(j), cols.get(j).text().trim());
            }
            rows.add(rowData);
        }

        return rows;
    }

    public static Map<String, Map<String, String>> parseBaseInfo(Document doc) {
        Map<String, Map<String, String>> baseInfo = new HashMap<>();

        Element h1 = doc.selectFirst("h1.awr:contains(WORKLOAD REPOSITORY report for)");
        if (h1 == null) return baseInfo;

        Element nextTable = h1;
        int count = 0;
        while (nextTable != null && count < 3) {
            nextTable = nextTable.nextElementSibling();
            if (nextTable != null && nextTable.tagName().equals("table")) {
                if (count == 0) {
                    baseInfo.put("awr_db_info", parseSingleRowTable(nextTable));
                } else if (count == 1) {
                    baseInfo.put("awr_host_info", parseSingleRowTable(nextTable));
                } else if (count == 2) {
                    baseInfo.put("awr_snapshot_info", parseMultiRowSnapshotTable(nextTable));
                }
                count++;
            }
        }

        return baseInfo;
    }

    public static Map<String, String> parseMultiRowSnapshotTable(Element table) {
        Map<String, String> result = new LinkedHashMap<>();
        Elements rows = table.select("tr");
        if (rows.size() < 2) return result;

        // 获取表头（从第1行获取，跳过 label 列）
        Elements headers = rows.get(0).select("th");
        List<String> colNames = new ArrayList<>();
        for (int i = 1; i < headers.size(); i++) {
            colNames.add(headers.get(i).text().trim());
        }

        // 处理每一行（从第2行开始）
        for (int i = 1; i < rows.size(); i++) {
            Elements cols = rows.get(i).select("td");
            if (cols.size() < 2) continue;

            String label = cols.get(0).text().replace(":", "").trim();
            for (int j = 1; j < cols.size() && j - 1 < colNames.size(); j++) {
                String key = label + "_" + colNames.get(j - 1);
                result.put(key, cols.get(j).text().trim());
            }
        }

        return result;
    }


    public static Map<String, String> parseSingleRowTable(Element table) {
        Map<String, String> result = new LinkedHashMap<>();

        Elements ths = table.select("tr").get(0).select("th");
        Elements tds = table.select("tr").get(1).select("td");

        for (int i = 0; i < ths.size() && i < tds.size(); i++) {
            String key = ths.get(i).text().trim();
            String value = tds.get(i).text().trim();
            result.put(key, value);
        }

        return result;
    }


}
